#!/usr/bin/env python
#-*- coding:utf-8 -*-

class Query(object):
    
    def __init__(self,**kwargs):
        if kwargs:
            self._sql=self.query(**kwargs)
    
    def _select(self,select):
        default='*'
        if select is None:
            return default
        elif isinstance(select,basestring):
            return select
        elif isinstance(select,(tuple,list)):
            return ','.join(str(field) for field in select)
        return default
    
    def _from(self,table):
        default=None
        if table is None:
            return default
        elif isinstance(table,basestring):
            return table
        elif isinstance(table,(tuple,list)):
            return ','.join(str(tb) for tb in table)
        return default
    
    def _where(self,where):
        default=None
        if where is None:
            return default
        elif isinstance(where,(basestring,tuple,list,dict)):
            if isinstance(where,basestring):
                return where
            elif isinstance(where,(tuple,list)):
                return ' AND '.join(where)
            elif isinstance(where,dict):
                def kv(k,v):
                    if isinstance(v,(int,long,float)):
                        return "%s=%s" % (k,v)
                    else:
                        return "%s='%s'" % (k,v)
                return ' AND '.join(map(lambda (k,v):kv(k,v),where.iteritems()))
        return default
    
    def _join(self,join):
        default=None
        if join is None:
            return default
        elif isinstance(join,basestring):
            return join
        elif isinstance(join,(tuple,list)):
            return ','.join(str(field) for field in join)
        return default
    
    def _group_by(self,group_by):
        default=None
        if group_by is None:
            return default
        elif isinstance(group_by,basestring):
            return group_by
        elif isinstance(group_by,(tuple,list)):
            return ','.join(str(field) for field in group_by)
        return default
    
    def _having(self,having):
        default=None
        if having is None:
            return default
        elif isinstance(having,basestring):
            return having
        elif isinstance(having,(tuple,list)):
            return ' AND '.join(str(field) for field in group_by)
        return default
    
    def _order_by(self,order_by):
        default=None
        if order_by is None:
            return default
        elif isinstance(order_by,basestring):
            return order_by
        elif isinstance(order_by,(tuple,list)):
            return ','.join(str(field) for field in order_by)
        elif isinstance(order_by,dict):
            return ','.join("%s %s" % (k,v or 'ASC') for k,v in order_by.iteritems())
        return default
    
    def _limit(self,limit):
        default=None
        if limit is None:
            return default
        elif isinstance(limit,(int,basestring)):
            return limit
        elif isinstance(limit,(tuple,list)):
            return ','.join(str(field) for field in limit)
        return default
    
    def query(self,fields=None,table=None,where=None,join=None,jtype='LEFT',group=None,having=None,order=None,limit=None,**kwargs):
        confs=(
              ('SELECT',(self._select,fields)),
              ('FROM',(self._from,table)),
              ('WHERE',(self._where,where)),
              ('JOIN',(self._join,join)),
              ('GROUP BY',(self._group_by,group)),
              ('HAVING',(self._having,having)),
              ('ORDER BY',(self._order_by,order)),
              ('LIMIT',(self._limit,limit)),
              )
        sql=''
        for conf in confs:
            cmd,(func,args)=conf
            res=func(args)
            if res is not None:
                if cmd=='JOIN':
                    sql+="%s %s %s " % (jtype,cmd,res)
                else:
                    sql+='%s %s ' % (cmd,res)
        return sql
    
    def sql(self,sql=None,**kwargs):
        if sql is not None:
            self._sql=sql
        elif not hasattr(self,'_sql'):
            self._sql=self.query(**kwargs)
        return self._sql

if __name__ == '__main__':
    q=Query(table='test',fields=('f1','f2','sum(f5)'),where=dict(f3=1,f4='two'),group=('f1','f2'))
    print q.sql()
    q=Query()
    print q.query(table='test',fields=('f1','f2','sum(f5)'),where=dict(f3=1,f4='two'),group=('f1','f2'),having='SUM(f5)>20',order=dict(f1='DESC',f2=None),limit=100)
    
